package com.suypower.carManage.json;

import com.jarp.db.DAO;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.suypower.carManage.VO.CarVO;
import com.suypower.carManage.comm.ConstUtil;
import com.suypower.carManage.comm.SQLHelp;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import net.sf.json.JSONArray;

import javax.servlet.ServletException;
import java.io.*;
import java.util.*;

public class BasicInformationJson extends ActionSupport {
    private String id;
    private String code;
    private String attributes;
    private String type;
    private String price;
    private String brand;
    private String displacement;
    private String purchase_date;
    private String use_date;
    private String company;
    private String department;
    private String manufacturer;
    private String driver;
    private String logo;
    private String gps;
    private String vehicleuse;
    private String states;
    private String start = "1";
    private String limit = "20";
    private String _dc;
    InputStream excelStream;
    ActionContext cxt = ActionContext.getContext();
    Map<String, Object> session = cxt.getSession();
    String userid = (String) session.get("userid");
    private String excelTitle;
    private String title = "车辆列表";

    DAO dao = new DAO();

    public BasicInformationJson(){
        System.out.println("数据初始化");
    }


    public String get_dc() {
        return _dc;
    }

    public void set_dc(String dc) {
        _dc = dc;
    }

    public String getVehicleuse() {
        return vehicleuse;
    }

    public void setVehicleuse(String vehicleuse) {
        this.vehicleuse = vehicleuse;
    }

    public String getStart() {
        return start;
    }

    public void setStart(String start) {
        this.start = start;
    }

    public String getLimit() {
        return limit;
    }

    public void setLimit(String limit) {
        this.limit = limit;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getAttributes() {
        return attributes;
    }

    public void setAttributes(String attributes) {
        this.attributes = attributes;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getPrice() {
        return price;
    }

    public void setPrice(String price) {
        this.price = price;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getDisplacement() {
        return displacement;
    }

    public void setDisplacement(String displacement) {
        this.displacement = displacement;
    }

    public String getPurchase_date() {
        return purchase_date;
    }

    public void setPurchase_date(String purchaseDate) {
        purchase_date = purchaseDate;
    }

    public String getUse_date() {
        return use_date;
    }

    public void setUse_date(String useDate) {
        use_date = useDate;
    }

    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    public String getStates() {
        return states;
    }

    public void setStates(String states) {
        this.states = states;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public String getManufacturer() {
        return manufacturer;
    }

    public void setManufacturer(String manufacturer) {
        this.manufacturer = manufacturer;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getLogo() {
        return logo;
    }

    public void setLogo(String logo) {
        this.logo = logo;
    }

    public String getGps() {
        return gps;
    }

    public void setGps(String gps) {
        this.gps = gps;
    }

    public String execute() {
        String res = "";
        int iStart = Integer.parseInt(start);
        int iPageSize = Integer.parseInt(limit);
        int iPage = iStart / iPageSize + 1;
        ArrayList<CarVO> list = new ArrayList<CarVO>();
        String sql = this.getSql();
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql, iPage, iPageSize);
//            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hash = (Hashtable) v.get(i);
                    CarVO carvo = new CarVO();
                    carvo.setId((String) hash.get("ID"));
                    carvo.setCode((String) hash.get("CODE"));
                    carvo.setAttributes((String) hash.get("ATTRIBUTES"));
                    carvo.setType((String) hash.get("TYPE"));
                    carvo.setPrice((String) hash.get("PRICE"));
                    carvo.setBrand((String) hash.get("BRAND"));
                    carvo.setDisplacement((String) hash.get("DISPLACEMENT"));
                    carvo.setPurchase_date((String) hash.get("PURCHASE_DATE"));
                    carvo.setUse_date((String) hash.get("USE_DATE"));
                    carvo.setCompany((String) hash.get("COMPANY"));
                    carvo.setDepartment((String) hash.get("DEPARTMENT"));
                    carvo.setManufacturer((String) hash.get("MANUFACTURER"));
                    carvo.setDriver((String) hash.get("DRIVER"));
                    String logo=(String) hash.get("LOGO");
                    carvo.setLogo(logo);
                    String gps=(String) hash.get("GPS");
                    carvo.setVehicleuse((String) hash.get("VEHICLEUSE"));
                    carvo.setStates((String) hash.get("STATES"));
                    list.add(carvo);
                }
                String totalCountSQL = "select count(1) CNT from (" + sql + " ) zz ";
                String totalCount = dao.getDataValue(ConstUtil.DBNAMEODS, totalCountSQL);
                JSONArray json = JSONArray.fromObject(list);
                ActionContext.getContext().put("data", "{totalCount:" + totalCount + ",data:" + json.toString() + "}");
            } else {
                ActionContext.getContext().put("data", "{totalCount:" + 0 + ",data:''}");
            }
        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        res = this.SUCCESS;
        return res;
    }

    public String getSql() {
        String sql = "SELECT * FROM tb_car t where 1=1";
        if (code != null && !code.equals("")) {
            sql += " AND t.code in (" +SQLHelp.splitStrFromWeb(code) + ") ";
        }
        if (vehicleuse != null && !vehicleuse.equals("")) {
            sql += " AND t.vehicleuse LIKE '%" + vehicleuse + "%' ";
        }
        if (company != null && !company.equals("")) {
            sql += " AND t.company in (" + SQLHelp.splitStrFromWeb(company) + ") ";
        }
        if (department != null && !department.equals("")) {
            sql += " AND t.department in (" + SQLHelp.splitStrFromWeb(department) + ") ";
        }
        if (type != null && !type.equals("")) {
            sql += " AND t.type in (" + SQLHelp.splitStrFromWeb(type) + ") ";
        }
        if (attributes != null && !attributes.equals("")) {
            sql += " AND t.attributes in (" + SQLHelp.splitStrFromWeb(attributes) + ") ";
        }
        if (states != null && !states.equals("")) {
            sql += " AND t.states in (" + SQLHelp.splitStrFromWeb(states) + ") ";
        }
        return sql;
    }

    public String getExcel() throws UnsupportedEncodingException, IOException, WriteException {
        System.out.println("导出Excel");
        excelStream = this.getExcelInputStream();
        return "excel";

    }

    public InputStream getExcelInputStream() throws IOException, WriteException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        create(out);
        return new ByteArrayInputStream(out.toByteArray());
    }

    public void create(OutputStream out) throws IOException, WriteException {
        excelTitle = title + ".xls";
        excelTitle = new String(excelTitle.getBytes(), "ISO8859-1");
        List<String> count_name = new ArrayList<String>();
        ;    //用来记录具体的列名
        count_name.add("车牌号码");
        count_name.add("资产属性");
        count_name.add("车辆类型");
        count_name.add("车辆原值(万)");
        count_name.add("品牌");
        count_name.add("排量");
        count_name.add("购买日期");
        count_name.add("使用日期");
        count_name.add("产权单位");
        count_name.add("使用部门");
        count_name.add("制造商");
        count_name.add("驾驶员姓名");
        count_name.add("是否使用公司标识");
        count_name.add("是否安装GPS");
        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet(title, 0);
        sheet.setRowView(0, 810);//设置大标题格式
        for (int i = 2; i < 100; i++) {
            sheet.setRowView(i, 320);
        }
        sheet.setColumnView(0, 18);
        sheet.setColumnView(1, 12);//设置第一行的宽度  //注意宽度和高度的单位不相同
        sheet.setColumnView(2, 30);
        sheet.setColumnView(3, 17);
        sheet.setColumnView(4, 17);
        sheet.setColumnView(5, 17);
        sheet.setColumnView(6, 17);
        sheet.setColumnView(7, 17);
        sheet.setColumnView(8, 25);
        sheet.setColumnView(9, 25);
        sheet.setColumnView(10, 35);
        sheet.setColumnView(11, 25);
        sheet.setColumnView(12, 25);
        sheet.setColumnView(13, 25);
        sheet.setColumnView(14, 25);
        sheet.setColumnView(15, 25);
        sheet.setColumnView(16, 25);
        sheet.setColumnView(17, 25);

        WritableFont wfc = new WritableFont(WritableFont.createFont("黑体"), 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        wcfFC.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        // wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        sheet.addCell(new Label(0, 0, title, wcfFC));

        //设置小标题格式
        WritableFont wfc2 = new WritableFont(WritableFont.createFont("黑体"), 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC2 = new WritableCellFormat(wfc2);
        wcfFC2.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC2.setBorder(Border.ALL, BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC2.setWrap(true);

        //设置普通文字格式
        WritableFont wfc3 = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC3 = new WritableCellFormat(wfc3);
        wcfFC3.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC3.setBorder(Border.ALL, BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC3.setWrap(true);
        sheet.addCell(new Label(0, 0, title, wcfFC));
        sheet.mergeCells(0, 0, count_name.size() - 1, 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {

                sheet.addCell(new Label(i, 1, count_name.get(i).split(",")[0], wcfFC2));
            } catch (Exception ex) {
            }
        }
        ArrayList<CarVO> list = new ArrayList<CarVO>();
        String sql = this.getSql();
        Vector v = null;
        try {
            v = dao.getDataSet(ConstUtil.DBNAME, sql);

        } catch (ServletException e) {
            e.printStackTrace();
        }
        if (v != null) {
            for(int i=1;i<v.size()+2;i++){
                sheet.setRowView(i, 400);
            }

            int hang = 2;     //数据应该写在多少行
            for (int i = 0; i < v.size(); i++) {
                ArrayList<String> rowlist = new ArrayList<String>();
                Hashtable hash = (Hashtable) v.get(i);
                CarVO carvo = new CarVO();
                carvo.setCode((String) hash.get("CODE"));
                carvo.setAttributes((String) hash.get("ATTRIBUTES"));
                carvo.setType((String) hash.get("TYPE"));
                carvo.setPrice((String) hash.get("PRICE"));
                carvo.setBrand((String) hash.get("BRAND"));
                carvo.setDisplacement((String) hash.get("DISPLACEMENT"));
                carvo.setPurchase_date((String) hash.get("PURCHASE_DATE"));
                carvo.setUse_date((String) hash.get("USE_DATE"));
                carvo.setCompany((String) hash.get("COMPANY"));
                carvo.setDepartment((String) hash.get("DEPARTMENT"));
                carvo.setManufacturer((String) hash.get("MANUFACTURER"));
                carvo.setDriver((String) hash.get("DRIVER"));
                String logo=(String) hash.get("LOGO");
                if(logo.equals("1")){
                    logo="是";
                }else if(logo.equals("2")){
                    logo="否";
                }else{
                    logo="";
                }
                carvo.setLogo(logo);
                String gps=(String) hash.get("GPS");
                if(gps.equals("1")){
                    gps="是";
                }else if(gps.equals("2")){
                    gps="否";
                }else{
                    gps="";
                }
                carvo.setGps(gps);
                rowlist.add(carvo.getCode());
                rowlist.add(carvo.getAttributes());
                rowlist.add(carvo.getType());
                rowlist.add(carvo.getPrice());
                rowlist.add(carvo.getBrand());
                rowlist.add(carvo.getDisplacement());
                rowlist.add(carvo.getPurchase_date());
                rowlist.add(carvo.getUse_date());
                rowlist.add(carvo.getCompany());
                rowlist.add(carvo.getDepartment());
                rowlist.add(carvo.getManufacturer());
                rowlist.add(carvo.getDriver());
                rowlist.add(carvo.getLogo());
                rowlist.add(carvo.getGps());
                for (int i2 = 0; i2 < rowlist.size(); i2++) {
                    String thisvalue = rowlist.get(i2);
                    if (thisvalue == null || thisvalue.equals("")) {
                        thisvalue = "";
                    }
                    sheet.addCell(new Label(i2, hang, thisvalue, wcfFC3));
                }
                hang++;
            }
            book.write();
            book.close();
        }
    }


    public String getMetadata(String code, String value) {
        String res = "";
        String sql = "SELECT t.name FROM tb_metadata t WHERE t.code='" + code + "' AND t.value='" + value + "'";
        //System.out.println("getMetadata:"+sql);
        try {
            res = dao.getDataValue(ConstUtil.DBNAME, sql);
        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return res;
    }

    public InputStream getExcelStream() {
        return excelStream;
    }

    public void setExcelStream(InputStream excelStream) {
        this.excelStream = excelStream;
    }

    public String getExcelTitle() {
        return excelTitle;
    }

    public void setExcelTitle(String excelTitle) {
        this.excelTitle = excelTitle;
    }
}